*****************************************************************
* Replication directory for                                   ***
* Prime locations                                             ***
* by Gabriel M. Ahlfeldt, Thilo N.H. Albers, Kristian Behrens ***
* Published in American Economic Review: Insights             ***
*****************************************************************
* 01/2025
* Stata
version 17.0

* This do file generates the final version of Table 1 and the coresponding full length table

* Load PL data
	u "$temp/US-CBSAs//p${SL}/PL-data.dta", clear
	
* Merge surrounding employment and population numbers
	merge 1:1 PLID_US using "$temp/GISUS_using/PLpop20km"
	tab _m
	drop _m
	merge 1:1 PLID_US using "$data_USMETROS/PL_emp20km/PL_emp20km", keepusing(PL_emp20km)
	tab _m
	drop if _m == 2
	drop _m	
	
* Final adjustments	
	drop STempshare_outsidePL
	replace PL_emp20km = PL_emp20km / 1000
	replace PL_pop20km = PL_pop20km / 1000
	label var PL_emp20km "Employment within 20km catchment area"
	label var PL_pop20km "Population within 20km catchment area"

* Update prime locations data set for toolkit
	capture mkdir "$dataoutput/US-CBSAs"
	save "$dataoutput/US-CBSAs//PL-data.dta", replace	
	export delimited using "$dataoutput/US-CBSAs//PL-data.csv", replace	
	
* Sort
	gsort -PL_emp
	
* Increase data set and add mean	
	local obs = _N+3
	display `obs'
	set obs `obs'
	
* Compute mean of all and top 10		
	foreach var of varlist PL_area PL_emp PL_empdens PL_*empshare *empshare_outsidePL PL_emp20km PL_pop20km {
		sum `var' if _n <= 10
		replace `var'=r(mean) if _n==_N-2
		sum `var' if metro_emp >= 1000
		replace `var'=r(mean) if _n==_N-1
		sum `var' if metro_emp < 1000
		replace `var'=r(mean) if _n==_N
	}
		
* Drop all non-top-10
	drop if _n > 10 & _n <= _N-3

* Label 	
	replace metro_name = "Mean, top-10 CBSAs" if _n==_N-2
	replace metro_name = "Mean, CBSA emp. >= 1M " if _n==_N-1
	replace metro_name = "Mean, CBSA emp. < 1M " if _n==_N
	tostring PL_area, replace force format(%9.2f)
	tostring PL_emp, replace force format(%12.0fc)
	tostring PL_empdens, replace force format(%12.0fc)
	tostring PL_pop20km, replace force format(%9.0fc)
	tostring PL_emp20km, replace force format(%9.0fc)
	foreach ind in MFG NT PS TS O  {
		tostring PL_`ind'empshare, replace force format(%9.1fc)
		tostring `ind'empshare_outsidePL, replace force format(%9.1fc)
	}
	
* Describe PL
	drop PL
	gen PL = ""
	replace PL = "Midtown" if metro_name == "New York-Newark-Jersey City, NY-NJ-PA" & PLrankByCity == 1 
	replace PL = "Lower Manhatten" if metro_name == "New York-Newark-Jersey City, NY-NJ-PA" & PLrankByCity == 2 
	replace PL = "The Loop" if metro_name == "Chicago-Naperville-Elgin, IL-IN-WI" & PLrankByCity == 1 
	replace PL = "Downtown D.C." if metro_name == "Washington-Arlington-Alexandria, DC-VA-MD-WV" & PLrankByCity == 1 
	replace PL = "Downtown Boston" if metro_name == "Boston-Cambridge-Newton, MA-NH" & PLrankByCity == 1 
	replace PL = "Financial District" if metro_name == "San Francisco-Oakland-Hayward, CA" & PLrankByCity == 1 
	replace PL = "City Center" if metro_name == "Philadelphia-Camden-Wilmington, PA-NJ-DE-MD" & PLrankByCity == 1 
	replace PL = "Seattle" if metro_name == "Seattle-Tacoma-Bellevue, WA" & PLrankByCity == 1 
	replace PL = "Downtown Los Angeles" if metro_name == "Los Angeles-Long Beach-Anaheim, CA" & PLrankByCity == 1 
	replace PL = "Downtown Atlanta" if metro_name == "Atlanta-Sandy Springs-Roswell, GA" & PLrankByCity == 1 

* Pick first city name in CBSA name
	split metro_name, parse("-")   
	replace metro_name = metro_name1
	
	label var PL "Prime location"
	keep metro_name PL PLrankUS PLrankByCity	PL_area PL_emp PL_empdens PL_*empshare *empshare_outsidePL  PL_emp20km PL_pop20km
	order metro_name PL PLrankUS PLrankByCity	PL_area PL_emp PL_empdens PL_TSempshare TSempshare_outsidePL PL_MFGempshare MFGempshare_outsidePL PL_NTempshare NTempshare_outsidePL PL_PSempshare PSempshare_outsidePL PL_Oempshare Oempshare_outsidePL  PL_emp20km PL_pop20km
	
* Write final version of Table 1
	capture mkdir "$tables/US-CBSAs"
	texsave metro_name PL PLrankUS PLrankByCity	PL_area PL_emp PL_empdens PL_TSempshare TSempshare_outsidePL PL_emp20km PL_pop20km ///
	using "$tables/US-CBSAs/TAB_1_PL_SUMMARY_A.tex", title("Prime locations in US cities") size("footnotesize")   width(16cm) align(llcccccc)  varlabels replace  frag ///
	footnote(".") 	
	
* Script ends	
